﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace LibrarySolution.DB
{
    class DBUtil
    {
        public static SqlCommand createCommand(string commandText,
            CommandType commandType)
        {
            SqlConnection conn = ConnFactory.getConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            return cmd;
        }

        /// <summary>
        ///  将DataTable转换为交错数组
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        protected static string[][] convertTableToArray(DataTable dataTable)
        {
            string[][] result;
            string[] resultColumn;
            DataRow tmpRow;
            int rowCount;
            int columnCount;
            if ((dataTable != null) && dataTable.Rows.Count > 0 && dataTable.Columns.Count > 0)
            {
                rowCount = dataTable.Rows.Count;
                columnCount = dataTable.Columns.Count;
                result = new string[rowCount][];

                for (int i = 0; i < rowCount; i++)
                {
                    resultColumn = new string[columnCount];
                    tmpRow = dataTable.Rows[i];
                    for (int j = 0; j < columnCount; j++)
                    {
                        if (!(tmpRow[j] is DBNull))
                        {
                            resultColumn[j] = tmpRow[j].ToString();
                        }
                        else
                        {
                            resultColumn[j] = "";
                        }
                    }
                    result[i] = resultColumn;
                }
                return result;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        ///  返回DataTable结果的数据
        /// </summary>
        /// <param name="commandText">执行的语句</param>
        /// <param name="commandType">command类型</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        protected static DataTable queryTable(string commandText,
            CommandType commandType, SqlParameter[] paras)
        {
            SqlCommand cmd = createCommand(commandText, commandType);
            DataSet sqlDataSet = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();

            if (paras != null) cmd.Parameters.AddRange(paras);
            try
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(sqlDataSet, "tempTable");
            }
            catch (Exception) { }
            finally
            {
                ConnFactory.closeConn(cmd.Connection);
                cmd.Parameters.Clear();
            }
            if (sqlDataSet.Tables.Count > 0)
                return sqlDataSet.Tables["tempTable"];
            else
                return null;
        }

        /// <summary>
        ///  command执行语句无返回
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="paras"></param>
        protected static void ExecuteCMDV(string commandText,
            CommandType commandType, SqlParameter[] paras)
        {
            SqlCommand cmd = createCommand(commandText, commandType);
            if (paras != null) cmd.Parameters.AddRange(paras);
            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception) { }
            finally
            {
                cmd.Connection.Close();
                cmd.Parameters.Clear();
            }
        }

        protected static string ExecuteCMD(string commandText,
            CommandType commandType, SqlParameter[] paras)
        {
            String result = null;
            SqlCommand cmd = createCommand(commandText, commandType);
            if (paras != null) cmd.Parameters.AddRange(paras);
            try
            {
                cmd.Connection.Open();
                result = (string)cmd.ExecuteScalar();
            }
            catch (InvalidCastException) {
                try { result = Convert.ToString((Int64)cmd.ExecuteScalar()); }
                catch (InvalidCastException)
                { result = Convert.ToString((Int32)cmd.ExecuteScalar()); }
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Parameters.Clear();
            }
            return result;
        }
    }
}
